package com.es.business.province;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.business.area.AreaBean;
import com.es.business.area.AreaBiz;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class ProvinceBiz {
	public boolean insertProvince(String areaid, String code, String value) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "insert into hrm_province (province_code, province_name, area_code) values(?,?,?)";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		sqlParam.add(value);
		sqlParam.add(areaid);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public int getProvinceCountByAreaCode(String area_code) throws Exception{
		Dao dao = new Dao();
		int result = 0;
		String sql = "select count(*) from hrm_province where area_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(area_code);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result = Integer.parseInt(rs.getString(1));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public PageBean searchProvince(String area_id, String code, String value, int curpage) throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = "select province_id, province_code, province_name, area_code from hrm_province ";
		String[] sqlp = {" province_code = ? ", " province_name = ? ", " area_code=? "};
		String and = " and ";
		String where = " where ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam2.add(code);
		sqlParam2.add(value);
		sqlParam2.add(area_id);
		boolean flag = false;
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
				if(flag){
					sql += and + sqlp[i];	
				}else{
					sql += where + sqlp[i];
					flag = true;
				}
				sqlParam.add(param);
			}
		}
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			pb.setParam(sqlParam2);
			for(int i = 0; i < pb.getMap().size(); ++i){
				HashMap<String, Object> map = pb.getMap().get(i);
				String areacode = map.get("area_code").toString();
				AreaBean areaBean = new AreaBiz().getAreaByCode(areacode);
				map.put("area_value", areaBean.getAreaValue());
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public boolean deleteProvinceById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "delete from hrm_province where province_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ProvinceBean getProvinceById(String id) throws Exception{
		Dao dao = new Dao();
		ProvinceBean result = null;
		String sql = "select province_code, province_name, area_code from hrm_province where province_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result = new ProvinceBean();
				result.setProvinceId(id);
				result.setAreaCode(rs.getString(3));
				result.setProvinceCode(rs.getString(1));
				result.setProvinceName(rs.getString(2));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ProvinceBean getProvinceByCode(String code) throws Exception{
		Dao dao = new Dao();
		ProvinceBean result = new ProvinceBean();
		String sql = "select province_id, province_name, area_code from hrm_province where province_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result.setProvinceCode(code);
				result.setProvinceId(rs.getString(1));
				result.setProvinceName(rs.getString(2));
				result.setAreaCode(rs.getString(3));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean modifyProvinceById(String areaCode, String code, String value, String provinceId) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "update hrm_province set area_code =?, province_code =? , province_name =? where province_id =?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(areaCode);
		sqlParam.add(code);
		sqlParam.add(value);
		sqlParam.add(provinceId);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<ProvinceBean> getProvinceList() throws Exception{
		Dao dao = new Dao();
		ArrayList<ProvinceBean> result = new ArrayList<ProvinceBean>();
		String sql = "select province_id, province_code, province_name, area_code from hrm_province";
		ArrayList<String> sqlParam = new ArrayList<String>();
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				ProvinceBean provinceBean = new ProvinceBean();
				provinceBean.setProvinceId(rs.getString(1));
				provinceBean.setProvinceCode(rs.getString(2));
				provinceBean.setProvinceName((rs.getString(3)));
				provinceBean.setAreaCode((rs.getString(4)));
				result.add(provinceBean);
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<ProvinceBean> getProvinceByAreaCode(String areaCode) throws Exception{
		Dao dao = new Dao();
		ArrayList<ProvinceBean> result = new ArrayList<ProvinceBean>();
		String sql = "select province_id, province_code, province_name from hrm_province where area_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(areaCode);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				ProvinceBean provinceBean = new ProvinceBean();
				provinceBean.setProvinceId(rs.getString(1));
				provinceBean.setProvinceCode(rs.getString(2));
				provinceBean.setProvinceName((rs.getString(3)));
				provinceBean.setAreaCode(areaCode);
				result.add(provinceBean);
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
}
